-- Crear query dinamico que haga rebuild y reorganize de todas las tablas e indices, hacer print en vez de exec

DECLARE @query nvarchar(max)
		,@object nvarchar(100)
		,@index int
		, @perc DECIMAL
		, @name nvarchar(100)
		, @tabla nvarchar(100)

DECLARE qControl CURSOR FOR
	SELECT object_id, index_id, avg_fragmentation_in_percent 
		FROM sys.dm_db_index_physical_stats(DB_ID('adventureworks2012'), null, null, null,'detailed')

OPEN qControl 
FETCH NEXT FROM qControl INTO @object, @index, @perc
WHILE @@FETCH_STATUS = 0
BEGIN 

	SELECT @name = name FROM sys.indexes where object_id = @object and index_id = @index
	SELECT @tabla = SCHEMA_NAME(SCHEMA_ID) + '.'  + NAME from sys.objects where object_id = @object
	
	if(@perc > 30) 
		BEGIN
			
			SET @query = N'ALTER INDEX @name on ' + @tabla + 'rebuild'
			PRINT @QUERY

		END
	else
		BEGIN
		
			SET @query = N'ALTER INDEX @name on ' + @tabla + 'reorganize'
			PRINT @QUERY

		END

	FETCH NEXT FROM qControl INTO @object, @index, @perc

END

CLOSE qControl
DEALLOCATE qControl


